﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using log4net;

namespace LanguageDetector.Data
{
    public static class LanguageStatDB
    {
        static ILog logger = LogManager.GetLogger(typeof(LanguageStatDB));

        #region fill method
        
        public static QueryResult<LanguageStat> Get(LanguageCriteria filter)
        {
            var res = new QueryResult<LanguageStat>();
            try
            {
                if (res.Errors.Count > 0)
                    res.Errors.ForEach(e => logger.Warn("Get validation error.", e));
                else
                {
                    var q = CreateGetQuery(filter);
                    res.Errors.AddRange(q.ExecuteReader(dr =>
                    {
                        var s = new LanguageStat();
                        s.ID = dr.GetInt32(0);
                        s.ISO2 = dr.GetString(1);
                        s.Name = dr.GetString(2);
                        s.BiGramTotal = dr.GetInt32(3);
                        s.IncludeASCII = dr.GetBoolean(4);
                        res.Add(s);
                    }));
                    res.Errors.ForEach(e => logger.Error("Get error.", e));
                }
            }
            catch (Exception ex)
            {
                res.Errors.Add(ex);
                logger.Error("Error fetching language_stat!", ex);
            }
            return res;
        }

        static Query CreateGetQuery(LanguageCriteria filter)
        {
            var sb = new StringBuilder();
            if (!filter.IDs.IsNullOrEmpty())
            {
                sb.Append("id IN (");
                filter.IDs.ForEach(id => sb.AppendFormat("{0},", id));
                sb.Length--;
                sb.Append(") and ");
            }
            if (!filter.ISOs.IsNullOrEmpty())
            {
                sb.Append("iso2 IN (");
                filter.ISOs.ForEach(iso => sb.AppendFormat("'{0}',", iso));
                sb.Length--;
                sb.Append(") and ");
            }
            if (!filter.Names.IsNullOrEmpty())
            {
                sb.Append("name IN (");
                filter.Names.ForEach(n => sb.AppendFormat("'{0}',", n));
                sb.Length--;
                sb.Append(") and ");
            }
            if (filter.MinTotal > 0)
                sb.AppendFormat("bigram_total >= {0} and ", filter.MinTotal);
            if (sb.Length > 4)
            {
                sb.Length -= 4;
                sb.Insert(0, "where ");
            }
            sb.Insert(0, "select id,iso2,name,bigram_total,include_ascii from language_stat ");
            sb.Append(";");
            var q = new Query() { Text = sb.ToString() };
            return q;
        }

        static void ValidateGet(LanguageCriteria filter, QueryResult<LanguageStat> res)
        {
            if (filter == null)
                res.Errors.Add(new ArgumentNullException("filter can not be null."));
        }

        #endregion

        #region save method

        public static List<Exception> Save(List<LanguageStat> stats)
        {
            return Save(stats, true);
        }

        public static List<Exception> Save(List<LanguageStat> stats, bool getNewIDs)
        {
            var errors = new List<Exception>();
            try
            {
                ValidateSave(stats, errors);
                if (errors.Count > 0)
                    errors.ForEach(e => logger.Warn("Error during save validation.", e));
                else
                {
                    if (getNewIDs)
                        SaveFetchIDs(stats, errors);
                    else
                        SaveOnly(stats, errors);
                    if(errors.Count>0)
                        errors.ForEach(e => logger.Error("Error during save.", e));
                }
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                logger.Error("Error saving language_stat.");
            }
            return errors;
        }

        static void SaveFetchIDs(List<LanguageStat> stats, List<Exception> errors)
        {
            var sb = new StringBuilder("begin transaction;\r\n");
            for (var i = 0; i < stats.Count; i++)
            {
                var s = stats[i];
                if (s.ID > 0)
                    sb.AppendFormat("update language_stat set iso2='{0}',name='{1}',bigram_total={2},include_ascii={3} where id={4};\r\nselect {4} as id;\r\n",
                        s.ISO2, s.Name, s.BiGramTotal, s.IncludeASCII ? 1 : 0, s.ID);
                else
                    sb.AppendFormat("insert into language_stat (iso2,name,bigram_total,include_ascii) values ('{0}','{1}',{2},{3});\r\nselect last_insert_rowid() as id;\r\n",
                        s.ISO2, s.Name, s.BiGramTotal, s.IncludeASCII ? 1 : 0);
            }
            sb.Append("commit transaction;\r\n");
            var q = new Query() { Text = sb.ToString() };
            var j = 0;
            errors.AddRange(q.ExecuteReader(dr =>
            {
                stats[j].ID = dr.GetInt32(0);
                j++;
            }));
        }

        static void SaveOnly(List<LanguageStat> stats, List<Exception> errors)
        {
            var sb = new StringBuilder("begin transaction;\r\n");
            for (var i = 0; i < stats.Count; i++)
            {
                var s = stats[i];
                if (s.ID > 0)
                    sb.AppendFormat("update language_stat set iso2='{0}',name='{1}',bigram_total={2},include_ascii={3} where id={4};\r\n",
                        s.ISO2, s.Name, s.BiGramTotal, s.IncludeASCII ? 1 : 0, s.ID);
                else
                    sb.AppendFormat("insert into language_stat (iso2,name,bigram_total,include_ascii) values ('{0}','{1}',{2},{3});\r\n",
                        s.ISO2, s.Name, s.BiGramTotal, s.IncludeASCII ? 1 : 0);
            }
            sb.Append("commit transaction;\r\n");
            var q = new Query() { Text = sb.ToString() };
            errors.AddRange(q.ExecuteNoneReader());
        }

        static void ValidateSave(List<LanguageStat> stats, List<Exception> errors)
        {
            if (stats.IsNullOrEmpty())
                errors.Add(new ArgumentNullException("stats can not be null or empty"));
            else
            {
                var rems = errors.RemoveAll(s => s == null);
                if (rems > 0)
                    logger.WarnFormat("stats contains {0} null items that has been removed.", rems);
            }
        }

        #endregion

        #region sync method
        
        public static List<Exception> Sync(LanguageStat langStat)
        {
            var errors = new List<Exception>();
            try
            {
                ValidateSync(langStat, errors);
                if (errors.Count > 0)
                    errors.ForEach(e => logger.Warn("Sync validation failed.", e));
                else
                {
                    var existing = SyncFetchExisting(langStat);
                    errors.AddRange(existing.Errors);

                    if (existing.IsNullOrEmpty()) //insert
                        errors.AddRange(LanguageStatDB.Save(new List<LanguageStat>() { langStat }));
                    else if (existing.Count == 1) //update
                    {
                        var cur = existing[0];
                        langStat.ID = cur.ID;
                        langStat.ISO2 = cur.ISO2;
                        langStat.Name = cur.Name;
                        langStat.IncludeASCII = cur.IncludeASCII;
                        langStat.BiGramTotal = cur.BiGramTotal;
                    }
                    else //if (existing.Count > 1)
                        errors.Add(new DataException(string.Format("Unable to sync because more than 1 language matches ISO=={0} or Name=={1}", langStat.ISO2, langStat.Name)));

                    if (errors.Count > 0)
                        errors.ForEach(e => logger.Error("Error syncronizing langStat.", e));
                }
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                logger.Error("Error during sync.", ex);
            }
            return errors;
        }

        static QueryResult<LanguageStat> SyncFetchExisting(LanguageStat langStat)
        {
            var f = new LanguageCriteria();
            if (langStat.ID > 0)
                f.IDs = new List<int>() { langStat.ID };
            else
            {
                if (!string.IsNullOrEmpty(langStat.ISO2))
                    f.ISOs = new List<string>() { langStat.ISO2 };
                if (!string.IsNullOrEmpty(langStat.Name))
                    f.Names = new List<string>() { langStat.Name };
            }
            return LanguageStatDB.Get(f);
        }

        static void ValidateSync(LanguageStat langStat, List<Exception> errors)
        {
            if (langStat == null)
                errors.Add(new ArgumentNullException("langStat can not be null!"));
            else if (langStat.ID < 1 && string.IsNullOrEmpty(langStat.ISO2) && string.IsNullOrEmpty(langStat.Name))
                errors.Add(new InvalidOperationException("langStat's ID, ISO2, or Name must be available for synchronization"));
        }

        #endregion

        #region delete method

        public static List<Exception> Delete(List<int> IDs)
        {
            var errors = new List<Exception>();
            try
            {
                ValidateDelete(IDs, errors);
                if (errors.Count > 0)
                    errors.ForEach(e => logger.Warn("Error validating during delete.", e));
                else
                {
                    var sb = new StringBuilder("delete from language_stat where id IN (");
                    IDs.ForEach(id => sb.AppendFormat("{0},", id));
                    sb.Length--;
                    sb.Append(");");
                    var q = new Query() { Text = sb.ToString() };
                    errors.AddRange(q.ExecuteNoneReader());
                    errors.ForEach(e => logger.Error("Error during delete.", e));
                }
            }
            catch (Exception ex) 
            {
                errors.Add(ex);
                logger.Error("Delete error.", ex);
            }
            return errors;
        }

        static void ValidateDelete(List<int> IDs, List<Exception> errors)
        {
            if (IDs.IsNullOrEmpty())
                errors.Add(new ArgumentNullException("IDs can not be null or empty"));
        }

        #endregion
    }
}
